/**
 * #%L
 * SwiftUIDataAccessLayer
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2011 - 2012 Linkwithweb
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public 
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */
package com.linkwithweb.products.swiftui.dao.impl;

import java.beans.PropertyVetoException;
import java.io.OutputStream;
import java.security.KeyPair;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.test.jdbc.SimpleJdbcTestUtils;

import com.linkwithweb.products.swiftui.context.DBUpdateStatement;
import com.linkwithweb.products.swiftui.context.ExcelFile;
import com.linkwithweb.products.swiftui.context.GenericTypes;
import com.linkwithweb.products.swiftui.context.QueryContext;
import com.linkwithweb.products.swiftui.context.SystemContext;
import com.linkwithweb.products.swiftui.dao.PlatformDAO;
import com.linkwithweb.products.swiftui.dataobject.FrameworkApplication;
import com.linkwithweb.products.swiftui.dataobject.FrameworkBackup;
import com.linkwithweb.products.swiftui.dataobject.FrameworkEvent;
import com.linkwithweb.products.swiftui.dataobject.FrameworkEventScheduler;
import com.linkwithweb.products.swiftui.dataobject.FrameworkForm;
import com.linkwithweb.products.swiftui.dataobject.FrameworkFormField;
import com.linkwithweb.products.swiftui.dataobject.FrameworkLOV;
import com.linkwithweb.products.swiftui.dataobject.FrameworkLOVValue;
import com.linkwithweb.products.swiftui.dataobject.FrameworkMenu;
import com.linkwithweb.products.swiftui.dataobject.FrameworkReportColumn;
import com.linkwithweb.products.swiftui.dataobject.FrameworkRole;
import com.linkwithweb.products.swiftui.dataobject.FrameworkSystemColumn;
import com.linkwithweb.products.swiftui.dataobject.FrameworkTable;
import com.linkwithweb.products.swiftui.dataobject.FrameworkTextReport;
import com.linkwithweb.products.swiftui.dataobject.FrameworkTransaction;
import com.linkwithweb.products.swiftui.dataobject.FrameworkUpload;
import com.linkwithweb.products.swiftui.dataobject.FrameworkUser;
import com.linkwithweb.products.swiftui.dataobject.FrameworkWorkflow;
import com.linkwithweb.products.swiftui.export.excel.ExcelExporter;
import com.linkwithweb.products.swiftui.rowmapper.FormFieldRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkApplicationRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkBackupRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkEventRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkEventSchedulerRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkFormRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkRoleRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkTableRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkUploadRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkUserRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkWorkflowRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.FrameworkWorkflowTransactionRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.LOVRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.LOVValueRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.MenuRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.ReportColumnRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.ReportComponentRowMapper;
import com.linkwithweb.products.swiftui.rowmapper.SystemColumnRowMapper;
import com.linkwithweb.products.swiftui.spring.jdbc.BatchResultSetExtractor;
import com.linkwithweb.products.swiftui.spring.jdbc.DatabaseResult;
import com.linkwithweb.products.swiftui.spring.jdbc.MapObject;
import com.linkwithweb.products.swiftui.spring.jdbc.TableComponentRowMapper;
import com.linkwithweb.products.swiftui.utils.DB2Sql;
import com.linkwithweb.products.swiftui.utils.FreeMarkerEngine;
import com.linkwithweb.products.swiftui.utils.JCryptionUtil;
import com.linkwithweb.products.swiftui.utils.ObjectSerializer;
import com.linkwithweb.products.swiftui.utils.SystemConstants;
import com.linkwithweb.products.swiftui.vo.TableMetadata;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * @author ashwin
 * 
 */
@Repository(value = "platformDAO")
public class PlatformDAOImpl extends AbstractDAOImpl implements PlatformDAO {

	private static final transient Log logger = LogFactory.getLog(PlatformDAOImpl.class);

	public void loadMetaData() {
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.framework.dao.PlatformDAO#loadResult(java.lang.String)
	 */
	public DatabaseResult loadResult(String sql, String dataSourceTemplate) {
		return loadResult(sql, dataSourceTemplate, new ArrayList());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * com.linkwithweb.framework.util.dao.PlatformDAO#loadResult(java.lang.
	 * String
	 * , long, long)
	 */
	public DatabaseResult loadResult(String sql, String dataSourceTemplate, long fromIndex, long toIndex) {
		DatabaseResult result = new DatabaseResult();

		TableComponentRowMapper rowMapper = new TableComponentRowMapper();
		rowMapper.setStartIndex(fromIndex);
		rowMapper.setEndIndex(toIndex);
		rowMapper.setLoadData(true);

		List resultList = new ArrayList();
		resultList = (List) getJdbcTemplate(dataSourceTemplate).query(sql, rowMapper);
		result.setData(resultList);
		return result;
	}

	/**
	 * @param sql
	 * @param values
	 * @return
	 */
	public DatabaseResult loadResult(String sql, String dataSourceTemplate, List values) {
		TableComponentRowMapper rowMapper = new TableComponentRowMapper();
		rowMapper.setLoadData(true);

		BatchResultSetExtractor resultSetExtractor = new BatchResultSetExtractor(rowMapper);

		List resultList = new ArrayList();
		int resultCount = 0;
		String countSql = "SELECT COUNT(*) FROM (" + sql + ") xy";

		if (values != null && !values.isEmpty()) {
			// resultCount = queryForInt(countSql, dataSourceTemplate, values);

			resultList = (List) getJdbcTemplate(dataSourceTemplate).query(sql, values.toArray(new Object[0]),
					resultSetExtractor);
		} else {
			resultList = (List) getJdbcTemplate(dataSourceTemplate).query(sql, resultSetExtractor);
		}

		return resultSetExtractor.getDatabaseResult();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.northalley.framework.dao.PlatformDAO#loadResult(java.lang.String,
	 * java.util.List, long, long)
	 */
	public DatabaseResult loadResult(String sql, String dataSourceTemplate, List values, long fromIndex, long toIndex) {
		TableComponentRowMapper rowMapper = new TableComponentRowMapper();
		rowMapper.setLoadData(true);

		BatchResultSetExtractor resultSetExtractor = new BatchResultSetExtractor(rowMapper);

		List resultList = new ArrayList();
		int resultCount = 0;
		String countSql = "SELECT COUNT(*) FROM (" + sql + ") xy";

		if (values != null && !values.isEmpty()) {
			resultCount = queryForInt(countSql, dataSourceTemplate, values);

			resultList = (List) getJdbcTemplate(dataSourceTemplate).query(sql, values.toArray(new Object[0]),
					resultSetExtractor);
		} else {
			resultList = (List) getJdbcTemplate(dataSourceTemplate).query(sql, resultSetExtractor);
		}

		return resultSetExtractor.getDatabaseResult();
	}

	/**
	 * 
	 * 1) Create SQL Query to get total count of records from DB 2) Create the
	 * Query with LIMITS set so that we the Required Range of Records in output
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadResult(com.northalley.framework
	 *      .context.QueryContext)
	 */
	public DatabaseResult loadResult(QueryContext queryContext, String dataSourceTemplate) {
		String sql = "SELECT * FROM ";
		String countSQL = "SELECT COUNT(*) FROM ";
		// If entityType is of Table then We have to Generate Query
		if (StringUtils.equalsIgnoreCase(queryContext.getEntityContext().getEntityType(),
				GenericTypes.EntityTypes.TABLE_ENTITY_TYPE)) {
			sql = sql + queryContext.getEntityContext().getEntityName() + " WHERE 1=1 ";
			countSQL = countSQL + queryContext.getEntityContext().getEntityName();
		}

		// @TODO : Append Request Filter Parameters

		// Query the Database to get the count
		int totalRows = queryForInt(countSQL, dataSourceTemplate, new ArrayList());

		// Now Query With Offset and PageSize
		String offset = getRequestParamter("offset");
		String page_size = getRequestParamter("page_size");

		// Append these limits to Query
		sql = sql + " LIMIT " + offset + " " + page_size;
		// Create Instance of Corresponding Classes before that
		TableComponentRowMapper rowMapper = new TableComponentRowMapper();
		rowMapper.setLoadData(true);
		BatchResultSetExtractor resultSetExtractor = new BatchResultSetExtractor(rowMapper);
		getJdbcTemplate(dataSourceTemplate).query(sql, resultSetExtractor);

		resultSetExtractor.getDatabaseResult().setTotalRows(totalRows);

		return resultSetExtractor.getDatabaseResult();
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.framework.util.dao.PlatformDAO#loadValue(java.lang.String
	 *      , java.util.List)
	 */
	public int queryForInt(String sql, String dataSourceTemplate, List values) {
		return getJdbcTemplate(dataSourceTemplate).queryForInt(sql, values.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#query(java.lang.String,
	 *      java.lang.String, java.util.List)
	 */
	public List<Map<String, Object>> query(String sql, String dataSourceTemplate, List values) {
		return getJdbcTemplate(dataSourceTemplate).queryForList(sql, values.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#listAllTables(java.lang
	 *      .String)
	 */
	public List<TableMetadata> listAllTables(String dataSourceTemplateName) {
		List<TableMetadata> tables = new ArrayList<TableMetadata>();

		DatabaseMetaData meta;
		ResultSet rs = null;
		try {
			meta = getJdbcTemplate(dataSourceTemplateName).getDataSource().getConnection().getMetaData();

			rs = meta.getTables(null, null, null, new String[] { "TABLE" });
			FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
					.getListOfApplications().get(0);
			while (rs.next()) {
				TableMetadata metadata = new TableMetadata();
				String tableName = rs.getString("TABLE_NAME");
				String schemaName = rs.getString("TABLE_SCHEM");
				if (StringUtils.isBlank(schemaName)) {
					schemaName = rs.getString("TABLE_CAT");
				}

				if (StringUtils.equalsIgnoreCase(schemaName, "automata")) {
					/**
					 * If oracle and username is not equals to schema name then
					 * we are not going to generate reports for those tables
					 */
					continue;
				}
				metadata.setTableName(tableName);
				ResultSet rs1 = null;
				String primaryKeys = "";
				try {
					rs1 = meta.getPrimaryKeys(null, null, tableName);
					java.util.List list = new java.util.ArrayList();

					while (rs1.next()) {
						String columnName = rs1.getString("COLUMN_NAME");
						if (StringUtils.isBlank(primaryKeys)) {
							primaryKeys = columnName;
						} else {
							primaryKeys = primaryKeys + "," + columnName;
						}
					}
				} catch (Exception e) {
					// TODO: handle exception
				} finally {
					try {
						if (rs1 != null)
							rs1.close();
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				metadata.setPrimaryKeyColumnName(primaryKeys);
				metadata.setSchemaName(schemaName);
				tables.add(metadata);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return tables;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#getTableNames(java.lang
	 *      .String)
	 */
	public List<String> getTableNames(String dataSourceTemplate) {
		List<String> tableNames = new ArrayList<String>();

		DatabaseMetaData meta;
		ResultSet rs = null;
		try {
			meta = getJdbcTemplate(dataSourceTemplate).getDataSource().getConnection().getMetaData();

			rs = meta.getTables(null, null, null, new String[] { "TABLE" });

			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				tableNames.add(tableName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return tableNames;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#update(java.lang.String,
	 * boolean, java.lang.Object[])
	 */
	public void update(String sql, String dataSourceTemplate, Object[] args) {

		/**
		 * Evaluate SQL To addin any Runtime Filters
		 * 
		 */
		try {
			sql = FreeMarkerEngine.getInstance().evaluateString(sql);
		} catch (Exception e) {
			logger.debug(e.getMessage());
		}
		getJdbcTemplate(dataSourceTemplate).update(sql, args);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.northalley.framework.dao.PlatformDAO#saveAndGetKey(java.lang.String,
	 * boolean, java.lang.Object[])
	 */
	public Long saveAndGetKey(final String sql, String dataSourceTemplate, final Object[] args) {

		KeyHolder keyHolder = new GeneratedKeyHolder();

		getJdbcTemplate(dataSourceTemplate).update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int i = 1;
				for (Object object : args) {
					statement.setObject(i, object);
					i++;
				}

				return statement;

			}
		}, keyHolder);
		if (keyHolder.getKey() != null)
			return new Long(keyHolder.getKey().longValue());
		return null;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveReport(com.linkwithweb
	 *      .products.swiftui.dataobject.FrameworkTextReport)
	 */
	public void saveReport(FrameworkTextReport report) {
		/**
		 * 1.Insert into Framework Table if not exists .................
		 * 2.Insert into Text Report if not exists .....................
		 * 3.Create menu for schema if not exists ......................
		 * 4.Create menu if not exists
		 */

		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();
		genericQuery.append("SELECT count(*) FROM FRM_TABLE WHERE TABLE_ID = ?");
		arguments.add(report.getReportId());

		int count = getJdbcTemplate("frameworkJdbcTemplate").queryForInt(genericQuery.toString(), arguments.toArray());

		if (count > 0) {
			return;
		}

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append("INSERT INTO FRM_TABLE (TABLE_ID,TABLE_NAME,TABLE_TYPE,TABLE_SCHEMA,TABLE_SQL,TABLE_PRIMARY_KEY_COLUMN");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?");

		arguments.add(report.getReportId());
		arguments.add(report.getReportName());
		arguments.add("DBOBJECT");
		arguments.add(report.getFrameworkTable().getTableSchema());
		arguments.add(null);
		arguments.add(report.getFrameworkTable().getPrimaryKeyColumn());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);

		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());

		/**
		 * Now insert into report
		 */
		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append(
						"INSERT INTO FRM_TEXT_REPORT (REPORT_ID,REPORT_NAME,TABLE_ID,REPORT_DESCRIPTION,REPORT_HEADING,")
				.append(
						"REPORT_TYPE,FROZEN_COLUMN_COUNT,REPORT_DEFAULT_CSS,REPORT_DEFAULT_FILTER_STRING,REPORT_SEARCH_ACTION");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?,?,?");

		arguments.add(report.getReportId());
		arguments.add(report.getReportName());
		arguments.add(report.getReportId());
		arguments.add(report.getReportDescription());
		arguments.add(report.getReportHeading());
		arguments.add("xls");
		arguments.add(1);
		arguments.add(null);
		arguments.add(null);
		arguments.add(null);

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveReportColumns(com
	 *      .linkwithweb.products.swiftui.dataobject.FrameworkReportColumn)
	 */
	public void saveReportColumns(FrameworkReportColumn reportColumn, boolean updateExisting) {
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();

		if (!updateExisting) {
			genericQuery.append("SELECT count(*) FROM FRM_REPORT_COLUMNS WHERE COLUMN_ID = ? AND REPORT_ID = ?");
			arguments.add(reportColumn.getColumnId());
			arguments.add(reportColumn.getReportId());

			int count = getJdbcTemplate("frameworkJdbcTemplate").queryForInt(genericQuery.toString(),
					arguments.toArray());

			if (count > 0) {
				return;
			}
		} else {
			genericQuery.append("DELETE FROM FRM_REPORT_COLUMNS WHERE COLUMN_ID = ? AND REPORT_ID = ?");
			arguments.add(reportColumn.getColumnId());
			arguments.add(reportColumn.getReportId());

			getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
		}

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery.append("INSERT INTO FRM_REPORT_COLUMNS (COLUMN_ID,COLUMN_NAME,REPORT_ID,").append(
				"COLUMN_DESCRIPTION,COLUMN_LABEL,COLUMN_TYPE,COLUMN_GROUP,COLUMN_FORMULAE,").append(
				"EDITABLE,DISPLAYABLE,IS_FILTER_ENABLED,DISPLAY_WIDTH,FILTER_TYPE,COLUMN_ORDER,COLUMN_DEFAULT_VALUE");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?");

		arguments.add(reportColumn.getColumnId());
		arguments.add(reportColumn.getColumnName());
		arguments.add(reportColumn.getReportId());
		arguments.add(reportColumn.getColumnDescription());
		arguments.add(reportColumn.getColumnLabel());
		arguments.add(reportColumn.getColumnType());
		arguments.add(reportColumn.getColumnGroup());
		arguments.add(reportColumn.getColumnFormulae());
		arguments.add(reportColumn.isEditable());
		arguments.add(reportColumn.isDisplayable());
		arguments.add(reportColumn.isFilterEnabled());
		arguments.add(reportColumn.getDisplayWidth());
		arguments.add(reportColumn.getFilterType());
		arguments.add(reportColumn.getColumnOrder());
		arguments.add(reportColumn.getColumnDefaultValue());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveMenu(com.linkwithweb
	 *      .products.swiftui.dataobject.FrameworkMenu)
	 */
	public void saveMenu(FrameworkMenu menu) {
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();
		genericQuery.append("SELECT count(*) FROM FRM_MENU WHERE MENU_ID = ?");
		arguments.add(menu.getMenuId());

		int count = getJdbcTemplate("frameworkJdbcTemplate").queryForInt(genericQuery.toString(), arguments.toArray());

		if (count > 0) {
			return;
		}

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append("INSERT INTO FRM_MENU (MENU_ID,ENTITY_ID,MENU_NAME,MENU_TYPE,MENU_LABEL,MENU_DESCRIPTION,PARENT_MENU_ID,MENU_URL,MENU_ORDER");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?,?");

		arguments.add(menu.getMenuId());
		arguments.add(menu.getMenuId());
		arguments.add(menu.getMenuId());
		arguments.add("TREE");
		arguments.add(menu.getMenuLabel());
		arguments.add(menu.getMenuDescription());
		arguments.add(menu.getParentMenuId());
		arguments.add(menu.getMenuURL());
		arguments.add(menu.getMenuOrder());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveLovValue(com.linkwithweb
	 *      .products.swiftui.dataobject.FrameworkLOVValue)
	 */
	public void saveLovValue(FrameworkLOVValue lovValue) {
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();
		genericQuery.append("SELECT count(*) FROM FRM_LOV_VALUES WHERE LOV_ID = ? AND LOV_VALUE=?");
		arguments.add(lovValue.getLovId());
		arguments.add(lovValue.getLovValue());

		int count = getJdbcTemplate("frameworkJdbcTemplate").queryForInt(genericQuery.toString(), arguments.toArray());

		if (count > 0) {
			return;
		}

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery.append("INSERT INTO FRM_LOV_VALUES (LOV_ID,LOV_VALUE,LOV_VALUE_LABEL");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?");

		arguments.add(lovValue.getLovId());
		arguments.add(lovValue.getLovValue());
		arguments.add(lovValue.getLovValueLabel());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveLov(com.linkwithweb
	 *      .products.swiftui.dataobject.FrameworkLOV)
	 */
	public void saveLov(FrameworkLOV lov) {
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();
		genericQuery.append("SELECT count(*) FROM FRM_LOV WHERE LOV_ID = ?");
		arguments.add(lov.getLovID());

		int count = getJdbcTemplate("frameworkJdbcTemplate").queryForInt(genericQuery.toString(), arguments.toArray());

		if (count > 0) {
			return;
		}

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery.append("INSERT INTO FRM_LOV (LOV_ID,ENTITY_ID,LOV_NAME,LOV_DESCRIPTION,LOV_TYPE,").append(
				"LOV_TABLE,LOV_SQL,LOV_KEY_COLUMN,LOV_VALUE_COLUMN,LOV_LABEL");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?,?,?");

		arguments.add(lov.getLovID());
		arguments.add(lov.getEntityId());
		arguments.add(lov.getLovName());
		arguments.add(lov.getLovDescription());
		arguments.add(lov.getLovType());
		arguments.add(lov.getLovTable());
		arguments.add(lov.getLovSQL());
		arguments.add(lov.getLovKeyColumn());
		arguments.add(lov.getLovValueColumn());
		arguments.add(lov.getLovLabel());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadReport(java.lang.String)
	 */
	public FrameworkTextReport loadReport(String id) {
		List argumentList = new ArrayList();
		String reportSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_TEXT_REPORT WHERE REPORT_ID = ? ";
		argumentList.add(id);

		FrameworkTextReport report = null;
		try {
			report = (FrameworkTextReport) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(reportSQL,
					argumentList.toArray(), new ReportComponentRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Report:", e);
		}
		return report;
	}

	/**
	 * @param id
	 *            (non-Javadoc)
	 * 
	 *            Load Report Columns for Given Report Id
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadReportColumns(java.lang.
	 *      String)
	 */
	public List<FrameworkReportColumn> loadReportColumns(String id) {
		List argumentList = new ArrayList();
		String reportColumnSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_REPORT_COLUMNS WHERE REPORT_ID = ? ";
		argumentList.add(id);

		List<FrameworkReportColumn> reportColumns = new ArrayList<FrameworkReportColumn>();

		try {
			reportColumns = (List<FrameworkReportColumn>) getJdbcTemplate("frameworkJdbcTemplate").query(
					reportColumnSQL, argumentList.toArray(), new ReportColumnRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Report Columns :", e);
		}
		return reportColumns;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadFrameworkTable(java.lang
	 *      .String)
	 */
	public FrameworkTable loadFrameworkTable(String tableId) {
		List argumentList = new ArrayList();
		String tableSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_TABLE WHERE TABLE_ID = ? ";
		argumentList.add(tableId);

		FrameworkTable frameworkTable = null;
		try {
			frameworkTable = (FrameworkTable) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(tableSQL,
					argumentList.toArray(), new FrameworkTableRowMapper());
		} catch (Exception e) {
			logger.debug("Error while Loading Framework Table ", e);
		}
		return frameworkTable;
	}

	/**
	 * @param id
	 * @return
	 */
	public FrameworkForm loadForm(String id) {
		List argumentList = new ArrayList();
		String reportSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_FORM WHERE FORM_ID = ? ";
		argumentList.add(id);

		FrameworkForm form = null;
		try {
			form = (FrameworkForm) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(reportSQL,
					argumentList.toArray(), new FrameworkFormRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Report:", e);
		}
		return form;
	}

	/**
	 * @param id
	 * @return
	 */
	public List<FrameworkFormField> loadFormFields(String id) {
		List argumentList = new ArrayList();
		String reportColumnSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_FORM_FIELDS WHERE FORM_ID = ? ";
		argumentList.add(id);

		List<FrameworkFormField> formFields = new ArrayList<FrameworkFormField>();

		try {
			formFields = (List<FrameworkFormField>) getJdbcTemplate("frameworkJdbcTemplate").query(reportColumnSQL,
					argumentList.toArray(), new FormFieldRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Report Columns :", e);
		}
		return formFields;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadMenu(java.lang.String)
	 */
	public List<FrameworkMenu> loadMenu(String parentMenuSQL) {
		List<FrameworkMenu> frameworkMenus = new ArrayList<FrameworkMenu>();
		try {
			frameworkMenus = getJdbcTemplate("frameworkJdbcTemplate").query(parentMenuSQL, new MenuRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign Menu ", e);
		}

		return frameworkMenus;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadSystemColumns()
	 */
	public List<FrameworkSystemColumn> loadSystemColumns() {
		String systemColumnSQL = "SELECT * FROM FRM_SYSTEM_COLUMNS";

		List<FrameworkSystemColumn> systemColumns = new ArrayList<FrameworkSystemColumn>();
		try {
			systemColumns = getJdbcTemplate("frameworkJdbcTemplate")
					.query(systemColumnSQL, new SystemColumnRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign System Columns ", e);
		}

		return systemColumns;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#loadLOVValues(java.lang.String)
	 */
	public List<FrameworkLOVValue> loadLOVValues(String lovId) {
		FrameworkLOV frameworkLOV = null;

		try {
			logger.debug("LOV Base Query:----SELECT * FROM FRM_LOV WHERE LOV_ID = '" + lovId + "'");
			frameworkLOV = (FrameworkLOV) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(
					"SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema() + ".FRM_LOV WHERE LOV_ID = '"
							+ lovId + "'", new LOVRowMapper());
		} catch (Exception e) {
			logger.debug("LOV Base SQL Error", e);
			return Collections.EMPTY_LIST;
		}

		String lovValueSQL = "";

		if (StringUtils.equalsIgnoreCase(frameworkLOV.getLovType(), FrameworkLOV.LovTypes.SYSTEM)) {
			lovValueSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
					+ ".FRM_LOV_VALUES WHERE LOV_ID = '" + frameworkLOV.getLovID() + "'";
		} else if (StringUtils.equalsIgnoreCase(frameworkLOV.getLovType(), FrameworkLOV.LovTypes.DBOBJECT)) {
			lovValueSQL = "SELECT " + frameworkLOV.getLovLabel() + " as LOV_VALUE_LABEL  "
					+ frameworkLOV.getLovValueColumn() + " AS LOV_VALUE FROM " + frameworkLOV.getLovTable()
					+ " WHERE LOV_ID = '" + frameworkLOV.getLovID() + "'";
		} else {
			lovValueSQL = frameworkLOV.getLovSQL();
		}

		logger.debug("Running Below LOV Query on Below Datasource");
		;
		logger.debug("LOV Query:----" + lovValueSQL);
		logger.debug("Datasource:----" + getDatasourceName(lovValueSQL, "AUTOMATA"));
		try {
			List<FrameworkLOVValue> lovValuesList = Collections.EMPTY_LIST;
			lovValuesList = getJdbcTemplate(getDatasourceName(lovValueSQL, "AUTOMATA")).query(lovValueSQL,
					new LOVValueRowMapper());
			return lovValuesList;
		} catch (Exception e) {
			return Collections.EMPTY_LIST;
		}
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.northalley.framework.dao.PlatformDAO#getResultSet(java.lang.String,
	 *      boolean)
	 */
	public ResultSet getResultSet(String sql, String dataSourceTemplate) {
		Connection con = DataSourceUtils.getConnection(getJdbcTemplate(dataSourceTemplate).getDataSource());
		ResultSet resultSet = null;
		try {
			PreparedStatement statement = con.prepareStatement(sql);
			resultSet = statement.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return resultSet;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.linkwithweb.products.swiftui.dao.PlatformDAO#loadExcelFile(java.lang
	 * .String)
	 */
	public void loadExcelFile(ExcelFile excelFile) {
		if (StringUtils.equalsIgnoreCase(excelFile.getExcelType(), ExcelFile.ExcelTypes.COMPLEXTYPE)) {
			new ComplexExcelToDBMigrator().processFile(excelFile, getJdbcTemplate(SystemContext.getInstance()
					.getUserContext().getIdentifier()), this);
		} else {
			SimpleExcelToDBMigrator.processFile(excelFile, getJdbcTemplate(SystemContext.getInstance().getUserContext()
					.getIdentifier()), this);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.linkwithweb.products.swiftui.dao.PlatformDAO#loadAndStoreDataSource
	 * (java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	public void loadAndStoreDataSource(String dbUrl, String driver, String username, String password) {
		JdbcTemplate jdbcTemplate = null;

		if (templateMap.get(SystemContext.getInstance().getUserContext().getIdentifier()) != null) {
			jdbcTemplate = (JdbcTemplate) templateMap.get(SystemContext.getInstance().getUserContext().getIdentifier());
		} else {
			DataSource dataSource = new SingleConnectionDataSource(driver, dbUrl, username, password, true);
			jdbcTemplate = new JdbcTemplate(dataSource);
		}

		templateMap.put(SystemContext.getInstance().getUserContext().getIdentifier(), jdbcTemplate);

	}

	/**
	 * @param datasourceName
	 * @param dbUrl
	 * @param driver
	 * @param username
	 * @param password
	 */
	public void loadAndStoreDataSource(String datasourceName, String dbUrl, String driver, String username,
			String password) {
		JdbcTemplate jdbcTemplate = null;

		if (StringUtils.isBlank(datasourceName)) {
			datasourceName = SystemContext.getInstance().getUserContext().getIdentifier();
		}

		if (templateMap.get(datasourceName) != null) {
			jdbcTemplate = (JdbcTemplate) templateMap.get(datasourceName);
		} else {
			DataSource dataSource = null;
			ComboPooledDataSource comboPoolDataSource = null;
			if (datasourceName.startsWith("single_")) {
				dataSource = new SingleConnectionDataSource(driver, dbUrl, username, password, true);
			} else {
				comboPoolDataSource = new ComboPooledDataSource();// (driver,
				// dbUrl,
				// username,
				// password);
				try {
					comboPoolDataSource.setDriverClass(driver);
					comboPoolDataSource.setUser(username);
					comboPoolDataSource.setPassword(password);
					comboPoolDataSource.setJdbcUrl(dbUrl);
					comboPoolDataSource.setMinPoolSize(5);
					comboPoolDataSource.setAcquireIncrement(5);
					comboPoolDataSource.setAcquireRetryAttempts(30);
					comboPoolDataSource.setIdleConnectionTestPeriod(1800);
					comboPoolDataSource.setMaxIdleTime(1800);
					if (StringUtils.containsIgnoreCase(driver, "oracle")) {
						comboPoolDataSource.setPreferredTestQuery("select 1 from dual");
					} else if (StringUtils.containsIgnoreCase(driver, "mysql")) {
						comboPoolDataSource.setPreferredTestQuery("select 1");
					}

					dataSource = comboPoolDataSource;
				} catch (PropertyVetoException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}
			jdbcTemplate = new JdbcTemplate(dataSource);
		}

		templateMap.put(datasourceName, jdbcTemplate);
		templateMap.put(SystemContext.getInstance().getUserContext().getIdentifier(), jdbcTemplate);

	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#loadUser(java.lang.String
	 *      , java.lang.String)
	 */
	public FrameworkUser loadUser(String userName, String password) {
		FrameworkUser user = null;
		StringBuffer sql = new StringBuffer();

		try {
			KeyPair keys = SystemContext.getInstance().getKeys();
			if (false && keys != null) {
				String utf8String = new String(password.trim().getBytes("utf-8"));
				password = JCryptionUtil.decrypt(utf8String, keys);
				password = password.substring(2);
			}
		} catch (Exception e) {
			return null;
		}

		// sql.append("SELECT * FROM FRM_USERS WHERE USER_ID = ? AND USER_PASSWORD = ?");
		sql.append("SELECT * FROM FRM_USERS WHERE USER_ID = ?");

		List arguments = new ArrayList();
		arguments.add(userName);
		// arguments.add(password);

		try {
			user = (FrameworkUser) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(sql.toString(),
					arguments.toArray(), new FrameworkUserRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign user ", e);
		}

		if (user != null) {
			List<FrameworkApplication> applications = null;
			sql.setLength(0);
			sql
					.append("SELECT APP.* FROM FRM_APPLICATION APP JOIN FRM_USER_APP_ASSIGNMENT ASSIGN ON APP.APP_ID = ASSIGN.APP_ID WHERE ASSIGN.USER_ID = '"
							+ userName + "'");

			try {
				applications = (List) getJdbcTemplate("frameworkJdbcTemplate").query(sql.toString(),
						new FrameworkApplicationRowMapper());
			} catch (Exception e) {
				logger.debug("exception while Loadign user ", e);
			}

			user.setListOfApplications(applications);

			/**
			 * Now Load User Roles..
			 * select roles.* from frm_roles roles join frm_user_roles
			 * user_roles on roles.role_id = user_roles.role_id join frm_users
			 * users on users.user_id = user_roles.user_id where users.user_id =
			 */
			List<FrameworkRole> roles = null;
			sql.setLength(0);
			sql
					.append("select roles.* from frm_roles roles join frm_user_roles user_roles on roles.role_id = user_roles.role_id join frm_users users on users.user_id = user_roles.user_id where users.user_id = '"
							+ userName + "'");

			try {
				roles = (List) getJdbcTemplate("frameworkJdbcTemplate").query(sql.toString(),
						new FrameworkRoleRowMapper());
			} catch (Exception e) {
				logger.debug("exception while Loadign user roles.. ", e);
			}

			user.setListOfRoles(roles);

		}
		return user;
	}

	/**
	 * @param tableId
	 * @return
	 */
	public FrameworkWorkflow loadWorkflowForTable(String tableId) {
		StringBuffer sql = new StringBuffer();
		FrameworkWorkflow workflow = null;
		sql
				.append("SELECT workflow.* FROM FRM_WORKFLOW workflow join FRM_TABLE_WORKFLOW_ASSIGN assign on workflow.WORKFLOW_ID = assign.WORKFLOW_ID WHERE TABLE_ID = ? ");

		List arguments = new ArrayList();
		arguments.add(tableId);
		try {
			workflow = (FrameworkWorkflow) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(sql.toString(),
					arguments.toArray(), new FrameworkWorkflowRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign workflow for table " + tableId, e);
		}

		return workflow;
	}

	/**
	 * @param tableId
	 * @return
	 */
	public FrameworkTransaction loadLastWorkflowTransaction(String tableId) {
		StringBuffer sql = new StringBuffer();
		FrameworkTransaction transaction = null;
		sql
				.append("SELECT workflowTransaction.* from frm_workflow_transaction workflowTransaction join FRM_TABLE_WORKFLOW_ASSIGN assign on workflowTransaction.workflow_id = assign.workflow_id where assign.table_id = ? order by created_date desc limit 0,1 ");

		List arguments = new ArrayList();
		arguments.add(tableId);
		try {
			transaction = (FrameworkTransaction) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(
					sql.toString(), arguments.toArray(), new FrameworkWorkflowTransactionRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign workflow for table " + tableId, e);
		}

		return transaction;
	}

	/**
	 * @param transactionId
	 * @return
	 */
	public FrameworkTransaction loadWorkflowTransaction(String transactionId) {
		StringBuffer sql = new StringBuffer();
		FrameworkTransaction transaction = null;
		sql
				.append("SELECT workflowTransaction.* from frm_workflow_transaction workflowTransaction where TRANSACTION_ID = ? LIMIT 0,1 ");

		List arguments = new ArrayList();
		arguments.add(transactionId);
		try {
			transaction = (FrameworkTransaction) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(
					sql.toString(), arguments.toArray(), new FrameworkWorkflowTransactionRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign TRANSACTION for TRANSACITONID  " + transactionId, e);
		}

		return transaction;
	}

	/**
	 * Returns all workflow tasks for logged in user..
	 * 
	 * @return
	 */
	public List<FrameworkTransaction> loadWorkflowTasks() {
		StringBuffer sql = new StringBuffer();
		String roleId = SystemContext.getInstance().getUserContext().getRole();

		List<FrameworkTransaction> transactions = null;
		sql.append("SELECT workflowTransaction.* from frm_workflow_transaction workflowTransaction where ROLE_ID = ? ");

		List arguments = new ArrayList();
		arguments.add(roleId);
		try {
			transactions = (List) getJdbcTemplate("frameworkJdbcTemplate").queryForList(sql.toString(),
					arguments.toArray(), new FrameworkWorkflowTransactionRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign workflow transaction for role " + roleId, e);
		}

		return transactions;
	}

	/**
	 * @param transaction
	 * @return
	 */
	public FrameworkWorkflow loadWorkflowForTransaction(FrameworkTransaction transaction) {
		StringBuffer sql = new StringBuffer();
		FrameworkWorkflow workflow = null;
		sql
				.append("SELECT workflow.* FROM FRM_WORKFLOW workflow join frm_workflow_transaction workflowTransaction on workflowTransaction.workflow_id = workflow.workflow_id where workflowTransaction.TRANSACTION_ID = ? and workflowTransaction.IS_ACTIVE IS TRUE");

		List arguments = new ArrayList();
		arguments.add(transaction.getTransactionId());
		try {
			workflow = (FrameworkWorkflow) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(sql.toString(),
					arguments.toArray(), new FrameworkWorkflowRowMapper());
		} catch (Exception e) {
			logger.debug("exception while Loadign workflow for transaction " + transaction.getTransactionId(), e);
		}

		return workflow;
	}

	/**
	 * @param transaction
	 * @param approvalStatus
	 */
	public void changeTransactionStatus(FrameworkTransaction transaction, String newApprovalStatus) {
		// FrameworkWorkflow.ApprovalStatus
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();
		transaction = loadWorkflowTransaction(transaction.getTransactionId());

		FrameworkWorkflow workflow = loadWorkflowForTransaction(transaction);
		String roleId = SystemContext.getInstance().getUserContext().getRole();
		String userId = SystemContext.getInstance().getUserContext().getUserId();

		/**
		 * Update old transaction so that it becomes inactive...
		 */
		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append("UPDATE FRM_WORKFLOW_TRANSACTION SET IS_ACTIVE = '0',MODIFIED_DATE = ?,APPROVAL_STATUS = ?,ROLE_ID = ?,ACTED_BY = ? WHERE TRANSACTION_ID = ? AND IS_ACTIVE = '1' AND ROLE_ID = ?");
		arguments.add(new GregorianCalendar().getTime());
		arguments.add(newApprovalStatus);
		arguments.add(roleId);
		arguments.add(userId);
		arguments.add(transaction.getTransactionId());
		arguments.add(roleId);
		int count = getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());

		/**
		 * No Record found or duplicate request..
		 */
		if (count == 0) {
			return;
		}

		/**
		 * Create new transaction and assign to new role if there is next role
		 * present..
		 */
		String nextRoleToAssign = "none";
		if (StringUtils.equalsIgnoreCase(newApprovalStatus, FrameworkWorkflow.ApprovalStatus.APPROVED)) {
			try {
				nextRoleToAssign = getNextRole(roleId, workflow.getRoleApprovalSequence());
			} catch (Exception e) {
				logger.debug("Exception while assigning to new role:", e);
				SystemContext.getInstance().getUniqueMessage().set(e.getLocalizedMessage());
				SystemContext.getInstance().getMessages().add(e.getLocalizedMessage());
			}
		}

		/***
		 * Statements below this line will be executed only if this role has
		 * approved this transaction...
		 */
		if (!StringUtils.equalsIgnoreCase(newApprovalStatus, FrameworkWorkflow.ApprovalStatus.APPROVED)) {
			/**
			 * Send Notification..... and leave
			 */
			logger.debug(roleId + " rejected transaction:" + transaction.getTransactionId());
			SystemContext.getInstance().getUniqueMessage().set(
					roleId + " rejected transaction:" + transaction.getTransactionId());
			SystemContext.getInstance().getMessages().add(
					roleId + " rejected transaction:" + transaction.getTransactionId());
			return;
		}

		if (StringUtils.equalsIgnoreCase(nextRoleToAssign, "none")) {
			/**
			 * There are no more roles to assign so run the record in
			 * transaction...
			 */
			executeDBStatement(transaction);

			return;
		}

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append("INSERT INTO FRM_WORKFLOW_TRANSACTION (TRANSACTION_ID,WORKFLOW_ID,ROLE_ID,ACTED_BY,APPROVAL_STATUS,TRANSACTION_PAYLOAD,TABLE_NAME,ACTION");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?");
		String transactionId = transaction.getTransactionId();
		String workflowId = workflow.getWorkflowId();
		String approvalStatus = FrameworkWorkflow.ApprovalStatus.PENDING_APPROVAL;
		String payload = transaction.getTransactionPayload();

		arguments.add(transactionId);
		arguments.add(workflowId);
		arguments.add(nextRoleToAssign);
		arguments.add(transaction.getActedBy());
		arguments.add(approvalStatus);
		arguments.add(payload);
		arguments.add(transaction.getTableName());
		arguments.add(transaction.getAction());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());

		try {
			DBUpdateStatement stmt = loadDBStatement(transaction);
			SystemContext.getInstance().getUniqueMessage().set(
					stmt.getStatementType() + " Request successfully sent for approval!!!");
			SystemContext.getInstance().getMessages().add(
					stmt.getStatementType() + " Request successfully sent for approval!!!");
		} catch (Exception e) {

		}
	}

	private DBUpdateStatement loadDBStatement(FrameworkTransaction transaction) {
		DBUpdateStatement stmt = null;
		try {
			String payLoad = transaction.getTransactionPayload();
			stmt = (DBUpdateStatement) ObjectSerializer.convertStringToObject(payLoad);
		} catch (Exception e) {
			e.printStackTrace();
			logger.debug("Execption while completing transaction..", e);
		}
		return stmt;
	}

	/**
	 * @param transaction
	 */
	/**
	 * @param transaction
	 */
	private void executeDBStatement(FrameworkTransaction transaction) {
		try {
			String payLoad = transaction.getTransactionPayload();
			DBUpdateStatement stmt = (DBUpdateStatement) ObjectSerializer.convertStringToObject(payLoad);

			FrameworkApplication app = stmt.getApplication();
			loadAndStoreDataSource(stmt.getDatasourceName(), app.getDbUrl(), app.getDbDriver(), app.getDbUsername(),
					app.getDbPassword());

			if (StringUtils.containsIgnoreCase(stmt.getUpdateStatement(), "CREATE ")) {
				update(stmt.getUpdateStatement(), stmt.getDatasourceName(), new Object[] {});
			} else {
				update(stmt.getUpdateStatement(), stmt.getDatasourceName(), stmt.getArguments().toArray());
			}

			/**
			 * Generate Reports in UI
			 */
			if (StringUtils.containsIgnoreCase(stmt.getUpdateStatement(), "create "))
				connectAndGenerateReports(null, stmt.getDatasourceName());
		} catch (Exception e) {
			e.printStackTrace();
			logger.debug("Execption while completing transaction..", e);
		}
	}

	/**
	 * @param stmt
	 */
	public void addNewTransactionForTable(DBUpdateStatement stmt) {
		if (StringUtils.startsWithIgnoreCase(stmt.getTableName(), "FRM_")) {
			/**
			 * Insert and return
			 */
			if (StringUtils.containsIgnoreCase(stmt.getUpdateStatement(), "create ")) {
				stmt.setArguments(new ArrayList());
			}

			try {
				update(stmt.getUpdateStatement(), stmt.getDatasourceName(), stmt.getArguments().toArray());
			} catch (Throwable e) {
				logger.debug("Exception while runnign update statement..:", e);
				String errorMessage = e.getCause().getMessage();
				errorMessage = errorMessage.substring(errorMessage.lastIndexOf(":") + 1);
				SystemContext.getInstance().getUniqueMessage().set(errorMessage);
				SystemContext.getInstance().getMessages().add(errorMessage);
			} finally {
				return;
			}
		}

		/**
		 * Run statements on db and roll back after running so that if there are
		 * any errors we can capture them..
		 */
		FrameworkApplication app = stmt.getApplication();
		loadAndStoreDataSource("single_" + stmt.getDatasourceName(), app.getDbUrl(), app.getDbDriver(), app
				.getDbUsername(), app.getDbPassword());

		synchronized (logger) {
			try {
				getJdbcTemplate("single_" + stmt.getDatasourceName()).getDataSource().getConnection().setAutoCommit(
						false);
				/**
				 * Run statement now
				 */
				if (StringUtils.containsIgnoreCase(stmt.getUpdateStatement(), "create")) {
					stmt.setArguments(new ArrayList());
				}
				update(stmt.getUpdateStatement(), "single_" + stmt.getDatasourceName(), stmt.getArguments().toArray());
			} catch (Throwable e) {
				logger.debug("Exception while test inserting data:", e);
				String errorMessage = e.getCause().getMessage();
				errorMessage = errorMessage.substring(errorMessage.indexOf(":") + 1);
				SystemContext.getInstance().getUniqueMessage().set(errorMessage);
				SystemContext.getInstance().getMessages().add(errorMessage);
				SystemContext.getInstance().getMessages().add("Arguments:");
				for (Object argument : stmt.getArguments()) {
					if (argument == null) {
						SystemContext.getInstance().getMessages().add("null");
					} else {
						SystemContext.getInstance().getMessages().add(argument.toString());
					}
				}

				return;
			} finally {
				try {
					getJdbcTemplate("single_" + stmt.getDatasourceName()).getDataSource().getConnection().rollback();
					getJdbcTemplate("single_" + stmt.getDatasourceName()).getDataSource().getConnection()
							.setAutoCommit(true);
				} catch (Exception e) {
					// TODO: handle exception
				}
			}
		}

		FrameworkWorkflow workflow = loadWorkflowForTable(stmt.getTableName());

		if (workflow == null) {
			return;
		}

		String roleId = SystemContext.getInstance().getUserContext().getRole();
		String firstWorkflowRole = getFirstRole(workflow.getRoleApprovalSequence());

		if (false && StringUtils.equalsIgnoreCase(roleId, firstWorkflowRole)) {
			/**
			 * Insert and return
			 */
			if (StringUtils.containsIgnoreCase(stmt.getUpdateStatement(), "create ")) {
				stmt.setArguments(new ArrayList());
			}

			try {
				update(stmt.getUpdateStatement(), stmt.getDatasourceName(), stmt.getArguments().toArray());
			} catch (Throwable e) {
				logger.debug("Exception while runnign update statement..:", e);
				String errorMessage = e.getCause().getMessage();
				errorMessage = errorMessage.substring(errorMessage.lastIndexOf(":") + 1);
				SystemContext.getInstance().getUniqueMessage().set(errorMessage);
				SystemContext.getInstance().getMessages().add(errorMessage);
			}

		}

		/**
		 * Now create new transaction...in approval process
		 */

		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append("INSERT INTO FRM_WORKFLOW_TRANSACTION (TRANSACTION_ID,WORKFLOW_ID,ROLE_ID,ACTED_BY,APPROVAL_STATUS,TRANSACTION_PAYLOAD,TABLE_NAME,ACTION");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?");

		String transactionId = stmt.getStatementType() + "_" + System.nanoTime();
		String workflowId = workflow.getWorkflowId();
		String userId = SystemContext.getInstance().getUserContext().getUserId();
		String approvalStatus = FrameworkWorkflow.ApprovalStatus.PENDING_APPROVAL;
		String payload = ObjectSerializer.convertObjectToString(stmt);
		String action = stmt.getStatementType();

		arguments.add(transactionId);
		arguments.add(workflowId);
		arguments.add(firstWorkflowRole);
		arguments.add(userId);
		arguments.add(approvalStatus);
		arguments.add(payload);
		arguments.add(stmt.getTableName());
		arguments.add(action);
		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());

		SystemContext.getInstance().getUniqueMessage().set(
				stmt.getStatementType() + " Request successfully sent for approval!!!");
		SystemContext.getInstance().getMessages().add(
				stmt.getStatementType() + " Request successfully sent for approval!!!");

	}

	/**
	 * @param presentRole
	 * @param workflowRoleString
	 * @return
	 */
	public String getFirstRole(String workflowRoleString) {
		String[] roles = workflowRoleString.split(",");

		if (roles.length >= 2) {
			return roles[1].replaceAll("\\[", "").replaceAll("\\]", "");
		}
		return "none";
	}

	/**
	 * @param presentRole
	 * @param workflowRoleString
	 * @return
	 */
	public String getNextRole(String presentRole, String workflowRoleString) {
		String[] roles = workflowRoleString.split(",");

		int presentIndex = 0;

		for (int i = 0; i < roles.length; i++) {
			if (StringUtils.equalsIgnoreCase(roles[i].replaceAll("\\[", "").replaceAll("\\]", ""), presentRole)) {
				presentIndex = i;
				break;
			}
		}

		if (roles.length - 1 == presentIndex || roles.length < 2) {
			return "none";
		}

		if (presentIndex != 0) {
			return roles[presentIndex + 1].replaceAll("\\[", "").replaceAll("\\]", "");
		}

		return "none";
	}

	/**
	 * @param excelFile
	 */
	public void connectAndGenerateReports(ExcelFile excelFile, String datasourceName) {
		String clientIdentifier = SystemContext.getInstance().getUserContext().getIdentifier();

		if (StringUtils.isNotBlank(datasourceName)) {
			clientIdentifier = datasourceName;
		}

		if (clientIdentifier != null) {
			try {
				/**
				 * Retreive All Tables in This Connection
				 */
				List<TableMetadata> tablesInSchema = listAllTables(clientIdentifier);
				/**
				 * Generate Text Report for each Table
				 */
				for (TableMetadata metaData : tablesInSchema) {
					FrameworkTextReport newReport = new FrameworkTextReport();
					/**
					 * First Check table already exists in DB. If not create new
					 */

					String reportName = metaData.getSchemaName() + "_" + metaData.getTableName();
					if (!StringUtils.containsIgnoreCase(metaData.getTableName(), "PFT_")
							&& !StringUtils.equalsIgnoreCase(metaData.getSchemaName(), "transaction_tables")) {
						continue;
					}

					if (loadReport(reportName) == null) {
						newReport.setFrozenColumnCount(1);
						newReport.setReportId(reportName);
						newReport.setReportName(metaData.getTableName());
						newReport.setReportDescription(metaData.getTableName());
						newReport.setReportHeading(metaData.getTableName());
						newReport.setReportType("DBOBJECT");
						newReport.setTableId(reportName);

						FrameworkTable frameworkTable = new FrameworkTable();
						frameworkTable.setTableSchema(metaData.getSchemaName());
						frameworkTable.setTableId(reportName);
						frameworkTable.setPrimaryKeyColumn(metaData.getPrimaryKeyColumnName());
						frameworkTable.setTableName(metaData.getTableName());

						newReport.setFrameworkTable(frameworkTable);
						/**
						 * Now Save in Database
						 */
						saveReport(newReport);

						/**
						 * If excelFile is not null and excel table name is same
						 * as this tablename then generate report columns
						 */

						try {
							if (excelFile != null
									&& StringUtils.equalsIgnoreCase(excelFile.getTableName(), metaData.getTableName())) {

								int columnIndex = 0;
								for (String columnName : excelFile.getExcelTabs().get(0).getColumnNames()) {
									if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
										columnIndex = columnIndex + 1;
										continue;
									}

									String databaseColumnName = FrameworkReportColumn
											.convertAndGetGenericToDBColumnName(columnName);
									String excelColumnType = excelFile.getExcelTabs().get(0).getColumnTypes().get(
											columnIndex);
									String excelColumnGroupName = excelFile.getExcelTabs().get(0).getColumnGroupNames()
											.get(columnIndex);
									String columnId = reportName + "_" + databaseColumnName;

									FrameworkReportColumn reportColumn = new FrameworkReportColumn();
									reportColumn.setColumnLabel(columnName);
									reportColumn.setColumnId(columnId);
									reportColumn.setColumnDescription(columnName);
									reportColumn.setColumnGroup(excelColumnGroupName);
									reportColumn.setColumnName(databaseColumnName);
									reportColumn.setColumnOrder(columnIndex);
									reportColumn.setColumnType(SystemConstants.ColumnTypes.TEXTFIELD);

									if (StringUtils.equalsIgnoreCase("Text Box", excelFile.getExcelTabs().get(0)
											.getColumnTypes().get(columnIndex).trim())) {
										reportColumn.setColumnType(SystemConstants.ColumnTypes.TEXTFIELD);
									} else if (StringUtils.equalsIgnoreCase("Drop Down Box", excelFile.getExcelTabs()
											.get(0).getColumnTypes().get(columnIndex).trim())) {
										reportColumn.setColumnType(SystemConstants.ColumnTypes.LOV);
										reportColumn.setColumnDefaultValue(columnName);
									} else if (StringUtils.containsIgnoreCase(excelFile.getExcelTabs().get(0)
											.getColumnNames().get(columnIndex).trim(), "time")
											|| StringUtils.containsIgnoreCase(excelFile.getExcelTabs().get(0)
													.getColumnNames().get(columnIndex).trim(), "Date")
											|| StringUtils.containsIgnoreCase(excelFile.getExcelTabs().get(0)
													.getColumnNames().get(columnIndex).trim(), "Calendar")) {
										reportColumn.setColumnType(SystemConstants.ColumnTypes.DATETIME);
									}

									reportColumn.setDisplayable(true);
									reportColumn.setEditable(true);
									reportColumn.setFilterEnabled(true);
									reportColumn.setReportId(reportName);
									columnIndex = columnIndex + 1;

									saveReportColumns(reportColumn, true);
								}

							}// End of excel File check
						} catch (Exception e) {
							// TODO: handle exception
						}
					}

				}

				int maxOrder = queryForInt("SELECT MAX(MENU_ORDER) FROM FRM_MENU", "frameworkJdbcTemplate",
						new ArrayList());
				maxOrder = maxOrder + 1;
				/**
				 * Now add menu First Create Schema Menu and then Child Menu for
				 * that
				 */
				FrameworkMenu menu = new FrameworkMenu();
				if (tablesInSchema != null && !tablesInSchema.isEmpty()) {
					String schemaName = tablesInSchema.get(0).getSchemaName();
					menu.setMenuLabel(schemaName);
					menu.setMenuId(schemaName);
					menu.setMenuName(schemaName);
					menu.setMenuType("TREE");
					menu.setMenuOrder(maxOrder);
					menu.setParentMenuId("-1");
					saveMenu(menu);
				}

				for (TableMetadata metaData : tablesInSchema) {
					if (!StringUtils.containsIgnoreCase(metaData.getTableName(), "PFT_")
							&& !StringUtils.equalsIgnoreCase(metaData.getSchemaName(), "transaction_tables")) {
						continue;
					}

					menu = new FrameworkMenu();
					maxOrder = maxOrder + 1;
					menu.setMenuLabel(metaData.getTableName());
					menu.setMenuId(metaData.getTableName());
					menu.setMenuName(metaData.getTableName());
					menu.setParentMenuId(metaData.getSchemaName());
					menu.setMenuType("TREE");
					menu.setMenuURL("services/reportHandler?GCommandType=LiveGrid&amp;reportName="
							+ metaData.getSchemaName() + "_" + metaData.getTableName());
					menu.setMenuOrder(maxOrder);
					saveMenu(menu);
				}

			} catch (Exception e) {
				SystemContext.getInstance().getMessages().add(e.getLocalizedMessage());
				e.printStackTrace();
			}
		}
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#executeScript(java.lang
	 *      .String, java.lang.String)
	 */
	public void executeScript(String sqlScriptPath, String dataSourceName) {
		SimpleJdbcTemplate simpleJdbcTemp = new SimpleJdbcTemplate(getJdbcTemplate(dataSourceName).getDataSource());
		SimpleJdbcTestUtils.executeSqlScript(simpleJdbcTemp, new FileSystemResource(sqlScriptPath), false);
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#generateReport(java.
	 *      lang.String, java.lang.String, java.util.Map, java.io.OutputStream)
	 */
	public void generateReport(String reportName, String sql, Map<String, Object> parameters, OutputStream out) {
		String sqlExportString = SystemContext.getInstance().getSqlExportMap().get(reportName);
		String[] sqlParts = sqlExportString.split("___");
		String dataSourceName = sqlParts[0];
		String sqlString = sqlParts[1];

		ExcelExporter excelExporter;
		try {
			excelExporter = new ExcelExporter(getJdbcTemplate(dataSourceName).getDataSource().getConnection());
			excelExporter.generateReport(reportName, sqlString, Collections.EMPTY_MAP, out);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @param tagName
	 * @return
	 */
	public List<FrameworkUpload> getFilesByTag(String tagName) {
		List argumentList = new ArrayList();
		String reportSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_UPLOADS WHERE IDENTIFIER = ? ";
		argumentList.add(SystemContext.getInstance().getUserContext().getFrameworkUser().getIdentifier());

		if (StringUtils.isNotBlank(tagName)) {
			reportSQL = reportSQL + " AND UPLOAD_TAG = '," + tagName + "'";
		}

		List<FrameworkUpload> listOfUploads = null;
		try {
			listOfUploads = getJdbcTemplate("frameworkJdbcTemplate").query(reportSQL, argumentList.toArray(),
					new FrameworkUploadRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Files Uploaded:", e);
		}
		return listOfUploads;
	}

	/**
	 * @return
	 */
	public List<FrameworkBackup> loadBackups() {
		List argumentList = new ArrayList();
		String reportSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_BACKUPS WHERE IDENTIFIER = ? ";
		argumentList.add(SystemContext.getInstance().getUserContext().getFrameworkUser().getIdentifier());

		List<FrameworkBackup> listOfBackups = null;
		try {
			listOfBackups = getJdbcTemplate("frameworkJdbcTemplate").query(reportSQL, argumentList.toArray(),
					new FrameworkBackupRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Files Uploaded:", e);
		}
		return listOfBackups;
	}

	/**
	 * @param backupId
	 * @return
	 */
	public FrameworkBackup loadBackupById(String backupId) {
		List argumentList = new ArrayList();
		String reportSQL = "SELECT * FROM " + SystemContext.getInstance().getFrameworkSchema()
				+ ".FRM_BACKUPS WHERE IDENTIFIER = ? ";
		argumentList.add(SystemContext.getInstance().getUserContext().getFrameworkUser().getIdentifier());

		if (StringUtils.isNotBlank(backupId)) {
			reportSQL = reportSQL + " AND BACKUP_ID = ?";
			argumentList.add(backupId);
		}

		FrameworkBackup backup = null;
		try {
			backup = (FrameworkBackup) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(reportSQL,
					argumentList.toArray(), new FrameworkBackupRowMapper());
		} catch (Exception e) {
			logger.debug("Error While Loading Files Uploaded:", e);
		}
		return backup;
	}

	/**
	 * Called after executing a backup
	 * 
	 * @param backup
	 */
	public void saveBackupDetails(FrameworkBackup backup) {
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();
		String backupPath = "";
		try {
			backupPath = DB2Sql.backupDB("backup.sql", getJdbcTemplate("jdbcTemplate").getDataSource().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		String backupId = getDateString();

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery.append("INSERT INTO FRM_BACKUPS(BACKUP_ID,BACKUP_NAME,BACKUP_DESCRIPTION,BACKUP_PATH");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?");

		arguments.add(backupId);
		arguments.add(backupId + "_Backup");
		arguments.add(backupId + "_Backup");
		arguments.add(backupPath);

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#uploadFile(com.linkwithweb
	 *      .products.swiftui.dataobject.FrameworkUpload)
	 */
	public void uploadFile(FrameworkUpload upload) {
		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();

		genericQuery.setLength(0);
		arguments.clear();
		genericQuery
				.append("INSERT INTO FRM_UPLOADS(UPLOAD_ID,UPLOAD_DESC,UPLOAD_TYPE,UPLOAD_LABEL,UPLOAD_PATH,UPLOAD_TAG");
		/**
		 * Append default column names
		 */
		appendSystemColumnNames(genericQuery, null);
		genericQuery.append(")").append(" VALUES (?,?,?,?,?,?");

		arguments.add(upload.getUploadId());
		arguments.add(upload.getUploadDesc());
		arguments.add(upload.getUploadType());
		arguments.add(upload.getUploadLabel());
		arguments.add(upload.getUploadPath());
		arguments.add(upload.getUploadTag());

		/**
		 * Append system values
		 */
		appendSystemColumnValues(genericQuery, arguments, null);
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	public static void main(String[] args) {
		String roleString = "[none],[approver],[manager]";
		String lastRole = "approver";
		boolean isLastRole = false;
		String[] roles = roleString.split(",");
		if (StringUtils.contains(roles[roles.length - 1], lastRole)) {
			isLastRole = true;
		}
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#loadEvent(java.lang.
	 *      String, int)
	 */
	@Override
	public FrameworkEvent loadEvent(String schedulerId, int uniqueKey) {
		FrameworkEvent frameworkEvent = null;
		String eventSQL = "SELECT * FROM FRM_EVENTS WHERE SCHEDULER_ID = ? AND UNIQUE_KEY = ?";
		List arguments = new ArrayList();
		arguments.add(schedulerId);
		arguments.add(uniqueKey);
		try {
			frameworkEvent = (FrameworkEvent) getJdbcTemplate("frameworkJdbcTemplate").queryForObject(eventSQL,
					new FrameworkEventRowMapper(), arguments.toArray());
		} catch (Exception e) {
			logger.debug("exception while Loadign event ", e);
		}

		return frameworkEvent;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#loadEvents(java.lang
	 *      .String)
	 */
	@Override
	public List<FrameworkEvent> loadEvents(String schedulerId, Map<String, String> params) {
		List<FrameworkEvent> frameworkEvents = new ArrayList<FrameworkEvent>();
		String eventSQL = "SELECT * FROM FRM_EVENTS WHERE SCHEDULER_ID = ?";
		List arguments = new ArrayList();
		arguments.add(schedulerId);
		if (StringUtils.isNotBlank(params.get("sections"))) {
			eventSQL = eventSQL + " AND EVENT_USER_IDS IN (" + params.get("sections") + ")";
		}
		try {
			frameworkEvents = (List) getJdbcTemplate("frameworkJdbcTemplate").query(eventSQL,
					new FrameworkEventRowMapper(), arguments.toArray());
		} catch (Exception e) {
			logger.debug("exception while Loadign event ", e);
		}

		return frameworkEvents;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveOrUpdateEvent(com.linkwithweb
	 *      .products.swiftui.dataobject.FrameworkEvent)
	 */
	@Override
	public void saveOrUpdateEvent(FrameworkEvent frameworkEvent) {
		boolean saveMode = true;

		if (frameworkEvent.getUniqueKey() > 0) {
			saveMode = false;
		}

		StringBuffer genericQuery = new StringBuffer();
		List arguments = new ArrayList();

		if (saveMode) {
			genericQuery.setLength(0);
			arguments.clear();
			genericQuery
					.append("INSERT INTO FRM_EVENTS (EVENT_ID,SCHEDULER_ID,EVENT_NAME,START_DATE,END_DATE,DETAILS,EVENT_LATITUDE,EVENT_LONGITUDE,EVENT_LOCATION_ADDRESS,EVENT_USER_IDS");
			/**
			 * Append default column names
			 */
			appendSystemColumnNames(genericQuery, null);
			genericQuery.append(")").append(" VALUES (?,?,?,?,?,?,?,?,?,?");

			arguments.add(frameworkEvent.getEventId());
			arguments.add(frameworkEvent.getSchedulerId());
			arguments.add(frameworkEvent.getEventName());
			arguments.add(frameworkEvent.getEventStartDate());
			arguments.add(frameworkEvent.getEventEndDate());
			arguments.add(frameworkEvent.getEventDetails());
			arguments.add(frameworkEvent.getEventLatitude());
			arguments.add(frameworkEvent.getEventLongitude());
			arguments.add(frameworkEvent.getEventLocationAddress());
			arguments.add(frameworkEvent.getEventUserIds());

			/**
			 * Append system values
			 */
			appendSystemColumnValues(genericQuery, arguments, null);
		} else {
			/**
			 * Update the Event
			 */
			List<String> paramNames = new ArrayList<String>();

			genericQuery.setLength(0);
			arguments.clear();
			genericQuery.append("UPDATE FRM_EVENTS ");

			paramNames.add("SCHEDULER_ID");
			paramNames.add("EVENT_NAME");
			paramNames.add("START_DATE");
			paramNames.add("END_DATE");
			paramNames.add("DETAILS");
			paramNames.add("EVENT_LATITUDE");
			paramNames.add("EVENT_LONGITUDE");
			paramNames.add("EVENT_LOCATION_ADDRESS");
			paramNames.add("EVENT_USER_IDS");

			/**
			 * Append default column names
			 */
			appendSystemColumnNames(genericQuery, paramNames);

			arguments.add(frameworkEvent.getSchedulerId());
			arguments.add(frameworkEvent.getEventName());
			arguments.add(frameworkEvent.getEventStartDate());
			arguments.add(frameworkEvent.getEventEndDate());
			arguments.add(frameworkEvent.getEventDetails());
			arguments.add(frameworkEvent.getEventLatitude());
			arguments.add(frameworkEvent.getEventLongitude());
			arguments.add(frameworkEvent.getEventLocationAddress());
			arguments.add(frameworkEvent.getEventUserIds());

			/**
			 * Append system values
			 */
			appendSystemColumnValues(genericQuery, arguments, paramNames);
			genericQuery.append(" WHERE EVENT_ID = ?");
			arguments.add(frameworkEvent.getEventId());

		}
		getJdbcTemplate("frameworkJdbcTemplate").update(genericQuery.toString(), arguments.toArray());
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#loadEventScheduler(java
	 *      .lang.String)
	 */
	@Override
	public FrameworkEventScheduler loadEventScheduler(String schedulerId) {
		FrameworkEventScheduler frameworkEventScheduler = null;
		String eventSQL = "SELECT * FROM FRM_EVENT_SCHEDULER WHERE SCHEDULER_ID = ? ";
		List arguments = new ArrayList();
		arguments.add(schedulerId);
		try {
			frameworkEventScheduler = (FrameworkEventScheduler) getJdbcTemplate("frameworkJdbcTemplate")
					.queryForObject(eventSQL, new FrameworkEventSchedulerRowMapper(), arguments.toArray());
		} catch (Exception e) {
			logger.debug("exception while Loadign event scheduler ", e);
		}

		return frameworkEventScheduler;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#saveOrUpdateEventScheduler(com
	 *      .linkwithweb.products.swiftui.dataobject.FrameworkEventScheduler)
	 */
	@Override
	public void saveOrUpdateEventScheduler(FrameworkEventScheduler frameworkEventScheduler) {

	}

	/**
	 * @param event
	 */
	public void deleteEvent(FrameworkEvent event) {
		getJdbcTemplate("frameworkJdbcTemplate").update("DELETE FROM FRM_EVENTS WHERE EVENT_ID = ?",
				new Object[] { event.getEventId() });
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @see com.linkwithweb.products.swiftui.dao.PlatformDAO#getResourcesForScheduler
	 *      (java.lang.String)
	 */
	@Override
	public List<MapObject> getResourcesForScheduler(String sql) {
		List<MapObject> resourceList = new ArrayList<MapObject>();
		List<Map<String, Object>> returnList = new ArrayList<Map<String, Object>>();
		try {
			returnList = (List) getJdbcTemplate(getDatasourceName("test", "test")).queryForList(sql);
		} catch (Exception e) {
			logger.debug("exception while Loadign event ", e);
		}

		for (Map<String, Object> mapObject : returnList) {
			MapObject object = new MapObject();
			object.setValue((String) mapObject.get("USER_ID_COLUMN"));
			object.setKey((String) mapObject.get("USER_ID_COLUMN"));
			resourceList.add(object);
		}

		return resourceList;
	}
}
